--select * from fn_ct_get_wip_flush_process_paths ( 1000 )

ALTER  FUNCTION fn_ct_get_wip_flush_process_paths( @Begin_Process_Step_ID INT )

RETURNS @Results TABLE( Path_Id INT, Path NVARCHAR( 1000 ), Last_Process_Step_ID INT, Finished INT )
AS
BEGIN

	DECLARE @Process_Traverse_Table TABLE( Process_Step_ID INT, Next_Process_Step_ID INT )
	DECLARE @Process_Paths TABLE( Path_Id INT IDENTITY , Path NVARCHAR( 1000 ), Last_Process_Step_ID INT, Finished INT )
	
	DECLARE @Path NVARCHAR( 1000 )
	DECLARE @Process_Step_ID INT, @Next_Process_Step_ID INT, @Last_Process_Step_ID INT, @Path_ID INT, @Temp_Process_Step_ID INT, @Count INT
	DECLARE @Temp_Count INT
	DECLARE @TEMP_PATH_ID INT
	
	DECLARE @Process_Paths_Cursor CURSOR
	
	--initialisations
	SET @Path = ''
	--Inserting the first proces step into the path table
	INSERT INTO @Process_Paths( Path, Last_Process_Step_ID, Finished  ) VALUES( RTRIM( CONVERT( CHAR, @Begin_Process_Step_ID ) ), RTRIM( CONVERT( CHAR, @Begin_Process_Step_ID ) ), 0 )
	WHILE( 1 = 1 )
	BEGIN
		SET @Process_Paths_Cursor = CURSOR FOR SELECT Path_ID, Path, Last_Process_Step_ID FROM @Process_Paths WHERE Finished = 0
		OPEN @Process_Paths_Cursor
		FETCH NEXT FROM @Process_Paths_Cursor INTO @Path_ID, @Path, @Last_Process_Step_ID
	
		WHILE( @@FETCH_STATUS = 0 )
		BEGIN
			SELECT @Temp_Count = COUNT(*) FROM Process_Step_Traverse_Table WHERE ProcessStep_Id = @Last_Process_Step_ID
			IF( @TEMP_COUNT > 0 )
			BEGIN
				INSERT INTO @Process_Paths( Path, Last_Process_Step_ID, Finished ) SELECT @Path + ',' + RTRIM( CONVERT( CHAR, Next_ProcessStep_ID ) ), Next_ProcessStep_ID, 0  FROM Process_Step_Traverse_Table (NOLOCK) WHERE ProcessStep_Id = @Last_Process_Step_ID
				DELETE FROM @Process_Paths WHERE Path_ID = @Path_ID
			END
			ELSE
			BEGIN
				-- This Code is to get only one Path from Process to Finished Goods
				-- If all the possiable paths are required remove the Code in this Condition except the Update Query
				SELECT @TEMP_PATH_ID = Path_ID FROM @Process_Paths WHERE Finished = 1 AND Last_Process_Step_ID = ( SELECT Last_Process_Step_ID FROM @Process_Paths WHERE Path_ID = @Path_ID )
				IF( @@ROWCOUNT = 0 )
				BEGIN
					UPDATE @Process_Paths SET Finished = 1 WHERE Path_ID = @Path_ID
				END
				ELSE
				BEGIN
					DELETE FROM @Process_Paths WHERE Path_ID = @Path_ID
				END
			END
			FETCH NEXT FROM @Process_Paths_Cursor INTO @Path_ID, @Path, @Last_Process_Step_ID
		END
	
		CLOSE @Process_Paths_Cursor
		DEALLOCATE @Process_Paths_Cursor 
	
		SELECT @Path_ID = Path_ID FROM @Process_Paths WHERE Finished = 0 
		IF( @@ROWCOUNT = 0 )
		BEGIN
			BREAK --Break from the loop when all the paths have reached the FG stage
		END 
	END --end of while
	
	-- This query can used to remove all the paths to FG Except the first one
	--DELETE FROM @Process_Paths WHERE Path_Id in ( Select p2.Path_Id from @Process_Paths p1, @Process_Paths p2 where p1.Last_Process_Step_ID = P2.Last_Process_Step_ID and p2.Path_Id > p1.Path_Id  )
	
	--Deleting if any Last_Process_Step_ID ( At the end, The remaining Last_Process_Step_ID's in the @Process_Paths table
	--should belong to Finished Goods ) is not belongs to a Finished Good. 
	DELETE FROM @Process_Paths where Path_ID in  ( Select Path_ID from @Process_Paths , Process_Step where ProcessStep_ID = Last_Process_Step_ID and PS_Type_Name <> 'Finished Goods' )
	
	INSERT INTO @Results SELECT * FROM @Process_Paths ORDER BY Path_ID
	RETURN 

END --end of function 